/**
 * 路由
 */
const express = require('express');
const query = require('../config/dbConfig')
const router = express.Router()

// 统一返回格式
let responseData;
router.use(function (req, res,next) {
    responseData = {
        code: 200,  // 200表示正常， 其他数字表示异常
        message: ''
    }
    next()
})

// 查询数据库中有多少条数据
async function getStudentCount() {
    const sql = `SELECT COUNT(*) AS count FROM students`
    const result = await query(sql)
    // console.log('查询结果', result)
    return result[0].count
}

// 获取学生列表(分页查询)
router.get('/getStudentList', async (req, res)=>{
    let total = await getStudentCount();
    if(total===0) {
        responseData.message = '数据库中还没有数据'
        responseData.total = total
        responseData.msg = {
            total: total,
            data: []
        }
        res.json(responseData)
        return
    }
    const currentPage= parseInt(req.query.currentPage || '1');
    const pageSize= parseInt(req.query.pageSize || '10');
    let start = (currentPage-1)*pageSize
    const sql = 'SELECT * FROM students ORDER BY id DESC LIMIT ?, ?'
    query(sql, [start, pageSize]).then(result=>{
        responseData.message = '获取学生列表成功'
        responseData.msg = {
            total: total,
            data: result
        }
        res.json(responseData)
    }).catch(err=>{
        // console.log(err)
        responseData.code = 202
        responseData.message = '查询数据库错误' + err
        res.json(responseData)
    })
})

// 获取某一个学生的信息
router.get('/getOneStudent/:id', (req, res)=>{
    const id = req.params.id
    const sql = 'SELECT * FROM students WHERE id = ?'
    query(sql, [id]).then(result=>{
        // console.log('获取某一个学生的信息', result)
        if(result.length>0) {
            responseData.message = '获取学生信息成功'
            responseData.msg = {
                data: result[0]
            }
            res.json(responseData)
        } else {
            responseData.code = 201
            responseData.message = '该学生不存在'
            res.json(responseData)
        }
    }).catch(err=>{
        // console.log(err)
        responseData.code = 202
        responseData.message = '数据库错误' + err
        res.json(responseData)
    })
})


// 查询数据库中某学号是否存在
async function getStudentBySnumber(snumber) {
    const sql = `SELECT COUNT(*) AS count FROM students WHERE snumber = ${snumber}`
    const result = await query(sql)
    // console.log('查询结果', result)
    return result[0].count
}

// 添加学生信息
router.post('/addStudent', (req, res)=>{
    let { snumber, name, sex, classes, cscore, jscore, dbscore } = req.body
    getStudentBySnumber(snumber).then(count=>{
        if(count>0) {
            responseData.code = 201
            responseData.message = '数据库中已存在相同学号的学生信息, 添加学生信息失败'
            res.json(responseData)
        } else {
            const sql = 'INSERT INTO students (`snumber`, `name`, `sex`, `classes`, `cscore`, `jscore`, `dbscore`) VALUES (?,?,?,?,?,?,?)';
            query(sql, [snumber, name, sex, classes, cscore, jscore, dbscore]).then(result=>{
                // console.log('添加学生信息', result)
                if(result.affectedRows) {
                    responseData.message = '添加学生信息成功'
                    res.json(responseData)
                } else {
                    responseData.code = 201
                    responseData.message = '添加学生信息失败'
                    res.json(responseData)
                }
            }).catch(err=>{
                // console.log(err)
                responseData.code = 202
                responseData.message = '数据库错误' + err
                res.json(responseData)
            })
        }
    })
})


// 更新学生信息
router.put('/updateStudent/:id', (req, res)=>{
    const id = req.params.id;
    let studentInfo = req.body
    const sql = 'UPDATE students SET ? WHERE id = ?';
    query(sql, [studentInfo, id]).then(result=>{
        // console.log('更新学生信息', result)
        if(result.affectedRows) {
            responseData.message = '更新学生信息成功'
            res.json(responseData)
        } else {
            responseData.code = 201
            responseData.message = '更新学生信息失败'
            res.json(responseData)
        }
    }).catch(err=>{
        // console.log(err)
        responseData.code = 202
        responseData.message = '数据库错误' + err
        res.json(responseData)
    })
})


// 删除学生信息
router.delete('/deleteStudent/:id', (req, res)=>{
    const id = req.params.id;
    const sql = 'DELETE FROM students WHERE id = ?';
    query(sql, [id]).then(result=>{
        // console.log('删除学生信息', result)
        if(result.affectedRows) {
            responseData.message = '删除学生信息成功'
            res.json(responseData)
        } else {
            responseData.code = 201
            responseData.message = '删除学生信息失败'
            res.json(responseData)
        }
    }).catch(err=>{
        // console.log(err)
        responseData.code = 202
        responseData.message = '数据库错误' + err
        res.json(responseData)
    })
})


// 根据学号进行精确查询
router.get('/findBySnumber/:snumber', (req, res)=>{
    const snumber = req.params.snumber;
    const sql = 'SELECT * FROM students WHERE snumber = ?'
    query(sql, [snumber]).then(result=>{
        // console.log('获取某一个学生的信息', result)
        if(result.length>0) {
            responseData.message = '获取学生信息成功'
            responseData.msg = {
                total: result.length,
                data: result
            }
            res.json(responseData)
        } else {
            responseData.code = 201
            responseData.message = '该学生不存在'
            res.json(responseData)
        }
    }).catch(err=>{
        // console.log(err)
        responseData.code = 202
        responseData.message = '数据库错误' + err
        res.json(responseData)
    })
})


// 根据姓名进行模糊查询(分页查询)
router.get('/findByName', async (req, res)=>{
    let name = req.query.name;
    if(!name) {
        responseData.code = 201
        responseData.message = '姓名不能为空'
        res.json(responseData)
        return
    }
    name = '%' + name + '%'
    const totalSql = 'SELECT COUNT(*) AS count FROM students WHERE name LIKE ?'
    const countResult = await query(totalSql, [name]) ;
    const total = countResult[0].count
    if(total===0) {
        responseData.message = '数据库中不存在与其匹配的数据'
        responseData.msg = {
            total: total,
            data: []
        }
        res.json(responseData)
        return
    }

    const currentPage= parseInt(req.query.currentPage || '1');
    const pageSize= parseInt(req.query.pageSize || '10');
    let start = (currentPage-1)*pageSize
    const sql = 'SELECT * FROM students WHERE name LIKE ?  ORDER BY id DESC LIMIT ?, ?'
    query(sql, [name, start, pageSize]).then(result=>{
        // console.log('获取某一个学生的信息', result)
        if(result.length>0) {
            responseData.message = '查询学生信息成功'
            responseData.msg = {
                total: total,
                data: result
            }
            res.json(responseData)
        } else {
            responseData.code = 201
            responseData.message = '查询学生信息失败'
            res.json(responseData)
        }
    }).catch(err=>{
        // console.log(err)
        responseData.code = 202
        responseData.message = '数据库错误' + err
        res.json(responseData)
    })
})






// 导出学生列表
const nodeExcel = require('node-xlsx')  // npm install node-xlsx --save
const urlencode = require('urlencode')  // npm install urlencode --save
const config = [  // config 格式
    {
        name: 'snumber',
        title: '学号',
    },
    {
        name: 'name',
        title: '姓名',
    },
    {
        name: 'classes',
        title: '班级',
    },
    {
        name: 'sex',
        title: '性别',
    },
    {
        name: 'cscore',
        title: 'c语言成绩',
    },
    {
        name: 'jscore',
        title: 'java成绩',
    },
    {
        name: 'dbscore',
        title: '数据库成绩',
    },
    /*{
    name: 'create_time',
    title: '录入时间',
    format: function(value) {
            return value.replace(/\.\d+/, '');
        }
    }*/
]

router.get('/exportToExcel', (req, res) => {
    let excelName = '学生成绩表'
    let studentList = [];
    studentList.push(config.map(item => {
        return item.title
    }))

    //查询结果
    const sql = 'SELECT * FROM students'
    query(sql).then(function(data){
        data.forEach(list=>{
            let student = config.map(item=>{
                const value = list[item.name]
                // return item.format&&item.format(value) || value
                return value
            })
            studentList.push(student)
        })
        // console.log(studentList)

        let buffer = nodeExcel.build([{name: excelName, data: studentList}])
        let name = urlencode(excelName + '_' + (+new Date()) + '.xlsx', "utf-8")  // urlencode：返回字符串，此字符串中除了-_.之外的所有非字母数字字符都将被替换成百分号（%）后跟两位十六进制数，空格则编码为加号（+）
        // res.setHeader('Content-Type', 'application/octet-stream; charset=utf-8')
        res.setHeader('Content-Type', 'application/vnd.ms-excel;charset=utf-8')
        res.setHeader("Access-Control-Expose-Headers", "Content-Disposition")
        res.setHeader("Content-Disposition", "attachment; filename=" + name )  // Content-Disposition 文件名是中文显示

        res.end(buffer, 'binary')

    }).catch(err=>{
        responseData.code = 201
        responseData.message = '下载失败' + err
        res.json(responseData)
    })
})


module.exports = router
